package com.coamc.xlsunit;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.beetl.sql.core.OnConnection;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLReady;
import org.beetl.sql.core.db.ColDesc;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.KeyWordHandler;
import org.beetl.sql.core.db.TableDesc;
import org.beetl.sql.core.kit.CaseInsensitiveOrderSet;

/**
 * 直接使用BeetlSql来完成数据库访问
 * 
 * @author xiandafu
 *
 */
public class BeetlSqlDBAccess implements DBAccess {

    // DataSource ds = null;
    SQLManager sqlManager = null;
    KeyWordHandler keywordHandler = null;
    Log log = LogFactory.getLog(BeetlSqlDBAccess.class);

    public BeetlSqlDBAccess(SQLManager sqlManager) {
        this.sqlManager = sqlManager;
        keywordHandler = sqlManager.getDbStyle().getKeyWordHandler();
    }

    @Override
    public Map<String, Object> save(String tableName, Map<String, Object> row, List<String> id) {
        final TableDesc tableDesc = sqlManager.getMetaDataManager().getTable(tableName);
        StringBuilder sb = new StringBuilder("insert into ");
        sb.append(tableName);
        sb.append(" ( ");
        final Set<String> autos = new CaseInsensitiveOrderSet<String>();

        for (String col : row.keySet()) {
            if (tableDesc.containCol(col)) {
                if (tableDesc.getIdNames().contains(col)) {
                    // 主键，需要重新取回来
                    if (isAutoInc(tableDesc, col) && !isNumber(row.get(col))) {
                        autos.add(col);
                        continue;
                    }

                }
                sb.append(colName(col)).append(",");
            }

        }

        // 删除最后一个逗号
        sb.setLength(sb.length() - 1);
        sb.append(") values (");
        List<Object> values = new ArrayList<Object>();
        final List<String> allCols = new ArrayList<String>();
        for (String col : row.keySet()) {
            if (tableDesc.containCol(col)) {
                if (tableDesc.getIdNames().contains(col)) {
                    if (isAutoInc(tableDesc, col) && !isNumber(row.get(col))) {
                        continue;
                    }

                }
                sb.append("?,");
                values.add(row.get(col));
                allCols.add(col);
            }

        }
        sb.setLength(sb.length() - 1);
        sb.append(")");
        if (autos.size() > 1) {
            throw new RuntimeException("最多只支持一个自增主键");
        }
        final String sql = sb.toString();
        final Object[] paras = values.toArray();
        Object incColValue = sqlManager.executeOnConnection(new OnConnection<Object>() {
            @Override
            public Object call(Connection conn) throws SQLException {
                PreparedStatement ps = null;
                if (autos.isEmpty()) {
                    ps = conn.prepareStatement(sql);
                } else {
                    // 实际上只会有一个跟主键相关的自增主键
                    String incColName = (String)autos.toArray()[0];
                    ps = conn.prepareStatement(sql, new String[] {incColName});
                }
                int dbType = sqlManager.getDbStyle().getDBType();
                int i = 1;
                for (Object obj : paras) {
                    String colName = allCols.get(i - 1);
                    ColDesc desc = tableDesc.getColDesc(colName);
                    if (obj == null) {
                        ps.setNull(i++, desc.sqlType);
                        continue;
                    }
                    if (dbType == DBStyle.DB_ORACLE || dbType == DBStyle.DB_POSTGRES || dbType == DBStyle.DB_DB2
                        || dbType == DBStyle.DB_SQLSERVER) {
                        if (obj instanceof java.util.Date) {
                            obj = new Timestamp(((java.util.Date)obj).getTime());
                        }
                    }
                    try {
                        ps.setObject(i++, obj, desc.sqlType);
                    } catch (RuntimeException ex) {
                        throw new RuntimeException("保存出错，在 " + i + "位置，value=" + obj, ex);
                    } catch (SQLException ex) {
                        throw new RuntimeException("保存出错，在 " + i + "位置，value=" + obj, ex);
                    }

                }
                try {
                    ps.executeUpdate();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    throw ex;
                }

                if (!autos.isEmpty()) {
                    ResultSet seqRs = ps.getGeneratedKeys();
                    seqRs.next();
                    Object key = seqRs.getObject(1);
                    seqRs.close();
                    return key;
                } else {
                    return null;
                }
            }

        });
        // 主键的值
        Map<String, Object> ret = new HashMap<String, Object>();
        for (String key : tableDesc.getIdNames()) {
            if (autos.contains(key)) {
                ret.put(key, incColValue);
            } else {
                ret.put(key, row.get(key));
            }
        }

        System.out.println("准备数据 " + tableName + " values " + row);
        return ret;
    }

    @Override
    public Map<String, Object> findById(String tableName, Map<String, Object> id) {
        StringBuilder sb = new StringBuilder("select * from ");
        sb.append(tableName).append(" where ");
        List<Object> values = new ArrayList<Object>();
        for (String key : id.keySet()) {
            sb.append(key).append("=").append("? ").append(" and ");
            values.add(id.get(key));
        }
        // 删除最后给and
        sb.setLength(sb.length() - 4);
        List<Map> list = sqlManager.execute(new SQLReady(sb.toString(), values.toArray()), Map.class);
        if (list.size() == 0) {
            return null;
        } else {
            return toExcelData(list.get(0));
        }
    }

    @Override
    public List<Map<String, Object>> query(String tableName, String where, VariableTable vars) {
        StringBuilder sb = new StringBuilder("select * from ");
        sb.append(tableName).append(" where ").append(where);
        Map paras = new HashMap();
        paras.put("vars", vars);
        // 参考BeetlSQLVarRef 如何获取excel变量
        List<Map> list = sqlManager.execute(sb.toString(), Map.class, paras);
        // 不能强制转化到 return List<Map<String, Object>>
        List<Map<String, Object>> newList = new ArrayList<Map<String, Object>>();
        for (Map map : list) {
            Map<String, Object> newMap = map;
            newList.add(newMap);
        }

        return toExcelData(newList);
    }

    private boolean isAutoInc(TableDesc desc, String id) {
        ColDesc col = desc.getColDesc(id);
        return col.isAuto;
    }

    private boolean isNumber(Object o) {
        if (o == null) {
            return false;
        }

        if (o instanceof Number) {
            return true;
        } else {
            return false;
        }
    }

    private Map toExcelData(Map<String, Object> old) {
        Map map = new CaseInsensitiveHashMap();
        for (Entry<String, Object> entry : old.entrySet()) {
            String key = entry.getKey();
            String col = this.sqlManager.getNc().getColName(key);
            map.put(col, entry.getValue());
        }
        return map;
    }

    private List toExcelData(List<Map<String, Object>> list) {
        List newList = new ArrayList(list.size());
        for (Map<String, Object> map : list) {
            newList.add(toExcelData(map));
        }
        return newList;
    }

    private String colName(String name) {
        return keywordHandler.getCol(name);
    }

}
